USE [BaoCao]
GO
/****** Object:  StoredProcedure [dbo].[BaoCaoGiaiNgan]    Script Date: 07/05/2012 10:21:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[BaoCaoGiaiNgan]
	-- Add the parameters for the stored procedure here
	@CURRENCY nvarchar(3) ='All',
	@loaiKH int =0,
	@LDLINK nvarchar(200) ='',
	@MaKH nvarchar(200) ='',
	@dateFrom nvarchar(30),
	@dateTo nvarchar(30)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	declare @strQuery nvarchar(max) =''
	declare @str nvarchar(3) ='DI'

	if @CURRENCY!='All'
		select @strQuery+=' and LD.CURRENCY='''+@CURRENCY+''''
		
	if @loaiKH=1
		select @strQuery+=' and CUSTOMER.[SECTOR.ID]<=1002'
		
	if @loaiKH=2
		select @strQuery+=' and CUSTOMER.[SECTOR.ID]>1002'
	
	if @LDLINK!=''
		select @strQuery+=' and LD.[LD.LINK] like ''%'+@LDLINK+'%'''
	
	if @MaKH!=''
		select @strQuery+=' and LD.[CUSTOMER.ID] like ''%'+@MaKH+'%'''
    -- Insert statements for procedure here
    
	select @strQuery='select distinct LD.CURRENCY,LD.TERM,LD.[VALUE.DATE],LD.[FIN.MAT.DATE],LD.CATEGORY,LD.[LD.LINK],
			LD.[LD.ID],LD.AMOUNT,LD.[INTEREST.RATE],CUSTOMER.[SECTOR.ID],CUSTOMER.NAME
	from LD,CUSTOMER
	where
		CUSTOMER.[CUSTOMER.ID]=LD.[CUSTOMER.ID] and LD.[VALUE.DATE]=LD.[REPORT.DATE] and LD.TERM !='''' and LD.[LD.LINK]!='''' 
		and LD.[REPORT.DATE]>='''+@dateFrom+''' and LD.[REPORT.DATE]<='''+@dateTo+''''+@strQuery
	+ ' union	
	select distinct LD.CURRENCY,LD.TERM,LD.[VALUE.DATE],LD.[FIN.MAT.DATE],LD.CATEGORY,LD.[LD.LINK],
		LD.[LD.ID],LD.AMOUNT,LD.[INTEREST.RATE],CUSTOMER.[SECTOR.ID],CUSTOMER.NAME
	from LD,CUSTOMER,[LOAN.TRANSACTION]
	where
		LD.[CUSTOMER.ID]=CUSTOMER.[CUSTOMER.ID] and [LOAN.TRANSACTION].[LD.ID]=LD.[LD.ID] and LD.TERM !='''' and LD.[LD.LINK]!='''' 
		and LD.[VALUE.DATE]!=[LOAN.TRANSACTION].[REPORT.DATE] and [LOAN.TRANSACTION].[TRANS.TYPE]='''+@str+'''
		and [LOAN.TRANSACTION].[REPORT.DATE]>='''+@dateFrom+''' and [LOAN.TRANSACTION].[REPORT.DATE]<='''+@dateTo+'''' +@strQuery
		
	exec(@strQuery)		
	
END
